﻿

UPDATE SkillsPeriod SET DefaultDescription = 'Period01' FROM SkillsPeriod WHERE DefaultDescription = 'Period1'
UPDATE SkillsPeriod SET DefaultDescription = 'Period02' FROM SkillsPeriod WHERE DefaultDescription = 'Period2'
UPDATE SkillsPeriod SET DefaultDescription = 'Period03' FROM SkillsPeriod WHERE DefaultDescription = 'Period3'
UPDATE SkillsPeriod SET DefaultDescription = 'Period04' FROM SkillsPeriod WHERE DefaultDescription = 'Period4'
UPDATE SkillsPeriod SET DefaultDescription = 'Period05' FROM SkillsPeriod WHERE DefaultDescription = 'Period5'
UPDATE SkillsPeriod SET DefaultDescription = 'Period06' FROM SkillsPeriod WHERE DefaultDescription = 'Period6'
UPDATE SkillsPeriod SET DefaultDescription = 'Period07' FROM SkillsPeriod WHERE DefaultDescription = 'Period7'
UPDATE SkillsPeriod SET DefaultDescription = 'Period08' FROM SkillsPeriod WHERE DefaultDescription = 'Period8'
UPDATE SkillsPeriod SET DefaultDescription = 'Period09' FROM SkillsPeriod WHERE DefaultDescription = 'Period9'

GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [vSkillsCourseStudentOnlyAnswer_AvgPivot]
AS
--AMG 14/01/2024
--Only the Avg of the Course Student answers to the questions are returned - but Pivoted
--AMG 11/032024
--Changed Period1 to Period01 etc.
SELECT 
	AcademicYearID, OGP_StudentID,  CourseID, CourseCode,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, CourseID, CourseCode, AvgAnswerValue
		FROM vSkillsCourseStudentOnlyAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(AvgAnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable


GO



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [vSkillsCourseStudentOnlyAnswer_CountPivot]
AS
--AMG 14/01/2024
--Only the Count of the Answers to the Course questions are returned - but Pivoted
--AMG 11/032024
--Changed Period1 to Period01 etc.
SELECT 
	AcademicYearID, OGP_StudentID,  CourseID, CourseCode,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, CourseID, CourseCode, CountofAnswers
		FROM vSkillsCourseStudentOnlyAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(CountofAnswers)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable


GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [dbo].[vSkillsOverallStudentAnswer_AvgPivot]
AS
--AMG 14/01/2023
--The Overall Avg consists of the answers to the course questions, plus the answer to the student questions - Pivoted
--AMG 11/032024
--Changed Period1 to Period01 etc.
SELECT 
	AcademicYearID, OGP_StudentID,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, AvgAnswerValue
		FROM vSkillsOverallStudentAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(AvgAnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable


GO



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [vSkillsOverallStudentAnswer_CountPivot]
AS
--AMG 14/01/2023
--The Count of Answers to the the course questions, plus the answer to the student questions - Pivoted
--AMG 11/032024
--Changed Period1 to Period01 etc.
SELECT 
	AcademicYearID, OGP_StudentID,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, CountOfAnswers
		FROM vSkillsOverallStudentAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(CountOfAnswers)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable


GO




SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[vSkillsStudentOnlyAnswer_AvgPivot]
AS
--AMG 14/01/2023
--The Avg of the Student answers to the student questions - Pivoted
--AMG 11/032024
--Changed Period1 to Period01 etc.
SELECT 
	AcademicYearID, OGP_StudentID,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, AvgAnswerValue
		FROM vSkillsStudentOnlyAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(AvgAnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable
	

GO



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [vSkillsStudentOnlyAnswer_CountPivot]
AS
--AMG 14/01/2023
--The Count of the Student answers to the student questions - Pivoted
--AMG 11/032024
--Changed Period1 to Period01 etc.
SELECT 
	AcademicYearID, OGP_StudentID,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID,  CountofAnswers
		FROM vSkillsStudentOnlyAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(CountofAnswers)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable



GO



IF COL_LENGTH (N'SkillsPeriod', N'OrderBy') IS NOT NULL
BEGIN
    alter table SkillsPeriod drop column OrderBy
END 
GO
